<?php
include_once 'utilities/includes.php'; // better to alywas include everything, it will pnly do it once anyway.
include_once 'User.class.php';

class ns_dao
{
    
    public static function create() 
    {
        $d = new ns_dao();
        $d->Connect();
        return $d;
    }

    private $DB = null;
    
    public function __construct()
    {
        $this->DB = new Database(); // becasuse we have already store the login details in the database lass we don't have to do anything here
        
    }

    public function __destruct()
    {   
        $this->Disconnect(); // make sure the database connection hase been droped
        unset($this->DB);
    }
    
    public function Connect()
    {
        $this->DB->connect();
    }
    
    private function Disconnect()
    {
        $this->DB->disconnect();
    }
        
    public function query($sql,$key_column = null)
    {
        $this->Connect();        
        $result = $this->DB->query($sql,$key_column);
        $this->Disconnect();
        return $result;
    }

    public function update($sql)
    {
        $this->Connect();
        $rowCount = $this->DB->update($sql);
        $this->Disconnect();
        return $rowCount;
    }

    // -----------------------------------------------------------------------------------------------------------------------------------
    //  Stats classes below 
    // -----------------------------------------------------------------------------------------------------------------------------------
    
    public static function getUserSoftware($username)
    {
        $DB = self::create();
        $sql = "SELECT * FROM usersoftware WHERE username='{$username}'";
        $sql_result = $DB->query($sql);        
        unset($DB);
        if (isset($sql_result)) return $sql_result;
        return false;
    }
    
    public static function GetSoftwareNameViaID($id)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT SoftwareName FROM software WHERE SoftwareID = {$id} limit 1");
        unset($DB);
        if (isset($sql_result)) return $sql_result[0]['SoftwareName'];
        return false;
    }
    
    public static function CreateNewUser($username, $password, $firstName, $lastName, $email, $phone)
    {
        $DB = self::create();
        $sql = "INSERT INTO user (Username, Password, FirstName, LastName, Email, Phone, UserLevel) VALUES ('{$username}','{$password}','{$firstName}','{$lastName}','{$email}','{$phone}',1)";
        $sql_result = $DB->DB->insert($sql);
        unset($DB);
        return $sql_result;
    }
    
    public static function updateUser($username, $field, $value)
    {
        $DB = self::create();
        $rowCount = $DB->update("UPDATE user SET {$field} = '{$value}' WHERE Username = '{$username}'");
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateSoftwareDescription($softwareID, $field, $value)
    {
        $DB = self::create();
        $rowCount = $DB->update("UPDATE software SET {$field} = '{$value}' WHERE SoftwareID = '{$softwareID}'");
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateSoftwareVersion($versionID, $field, $value)
    {
        $DB = self::create();
        $rowCount = $DB->update("UPDATE softwareversion SET {$field} = '{$value}' WHERE SoftwareVersionID = '{$versionID}'");
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function CreateNewSoftwareDescription($softwareName, $softwareDescription, $category, $ownerUsername, $approved)
    {
        $DB = self::create();
        $sql = "INSERT INTO software (SoftwareName,SoftwareDescription,Category,OwnerUsername,Approved) VALUES ('{$softwareName}','{$softwareDescription}','{$category}','{$ownerUsername}','{$approved}')";
        $sql_result = $DB->DB->insert($sql);
        unset($DB);
        return $sql_result;
    }
    
    public static function updateSoftware($softwareID, $field, $value)
    {
        $DB = self::create();
        $rowCount = $DB->update("UPDATE software SET {$field} = '{$value}' WHERE SoftwareID = '{$softwareID}'");
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateUserByArray($username, $data)
    {
        //Modified since last revision to suit username as PK
        $insertData = array();
        foreach ($data as $key => $value)
            if ($key != "Username")
                $insertData[$key] = (is_null($value)) ? '' : $value;

        foreach ($insertData as $key => $value)
            if (!is_numeric($value) || $key == "Phone")
                $insertData[$key] = "'{$value}'";

        $sql   = "UPDATE user SET ";
        $blnFirst = true;
        foreach ($insertData as $key => $value){
            if ($blnFirst){
                $blnFirst = false;
                $sql = $sql . $key . "=" . $value;
            } else {
                $sql = $sql . "," . $key . " = " . $value;                
            }
        }        
        $sql .= " WHERE Username='" . $username . "'";
        $DB = self::create();
        $rowCount = $DB->update($sql);
        unset($DB);
        
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateSoftwareByArray($softwareID, $data)
    {
        $insertData = array();
        foreach ($data as $key => $value)
            if ($key != "SoftwareID")
                $insertData[$key] = (is_null($value) || trim($value) == "") ? '' : $value;

        foreach ($insertData as $key => $value)
            if (!is_numeric($value))
                $insertData[$key] = "'{$value}'";

        $sql   = "UPDATE `software` SET ";
        $blnFirst = true;
        foreach ($insertData as $key => $value){
            if ($blnFirst){
                $blnFirst = false;
                $sql = $sql . "`" . $key . "` = " . $value;
            } else {
                $sql = $sql . ",`" . $key . "` = " . $value;                
            }
        }        
        $sql = $sql . " WHERE `SoftwareID` = " . $softwareID;
        $DB = self::create();
        $rowCount = $DB->update($sql);
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function updateSoftwareVersionByArray($softwareVersionID, $data)
    {
        $insertData = array();
        foreach ($data as $key => $value)
            if ($key != "SoftwareVersionID")
                $insertData[$key] = (is_null($value) || trim($value) == "") ? '' : $value;

        foreach ($insertData as $key => $value)
            if (!is_numeric($value))
                $insertData[$key] = "'{$value}'";

        $sql   = "UPDATE `softwareVersion` SET ";
        $blnFirst = true;
        foreach ($insertData as $key => $value){
            if ($blnFirst){
                $blnFirst = false;
                $sql = $sql . "`" . $key . "` = " . $value;
            } else {
                $sql = $sql . ",`" . $key . "` = " . $value;                
            }
        }        
        $sql = $sql . " WHERE `SoftwareVersionID` = " . $softwareVersionID;
        $DB = self::create();
        $rowCount = $DB->update($sql);
        unset($DB);
        return ($rowCount >= 1) ? TRUE : FALSE;
    }
    
    public static function  UserByUsername($username)
    {
        logger::called();
        $DB = self::create();
        if (is_null($DB)) return null;
        $sql_result = $DB->query("select * from user where Username = '{$username}' limit 1");
        if (is_null($sql_result)) return null;
        if (count($sql_result) <= 0 ) return null;
        $U = User::createFromArray($sql_result[0]);
        unset($DB);
        return $U;
    }
    
    public static $AUTH_OK = "AuthOK";
    public static $AUTH_FAIL = "AuthFail";
    public static function authenticateUser($username, $password)
    {
        $DB = self::create();
        $sql_result = $DB->query("select * from user where Username = '{$username}' and Password = '{$password}' limit 1");
        $result = count($sql_result);
        unset($DB);
        return ($result >= 1) ? ns_dao::$AUTH_OK : ns_dao::$AUTH_FAIL;
    }
	
    public static function Users()
    {
        $DB = self::create();
        $sql_result = $DB->query("select * from User",'Username');
        
        foreach ($sql_result as $key => $user_row)
            $result[$key] = User::createFromArray($user_row);    

        unset($DB);
        
        return $result;
        
    }

    public static function GetUsersList()
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM user");

        foreach ($sql_result as $key => $user_row)
            $result[$key] = User::createFromArray($user_row);    

        unset($DB);        
        return $result;
    }
    
    public static function GetSoftwareVersionListByID($softwareID)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = '{$softwareID}' ORDER BY VersionNumber DESC LIMIT 20");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareVersion::createFromArray($software_row);    

        unset($DB);
        if (isset($result)) return $result;
        return false;
    }
    
    public static function GetSoftwareList()
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);    

        unset($DB);        
        return $result;
    }
    
    public static function GetApprovedSoftwareList()
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE Approved = 1");
        foreach ($sql_result as $key => $software_row)
            $result[$key] = SoftwareDescription::createFromArray($software_row);    

        unset($DB);        
        return $result;
    }
    
    public static function GetSoftwareDescription($softwareID)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE software.SoftwareID = {$softwareID} LIMIT 1");
        $result = SoftwareDescription::createFromArray($sql_result[0]);
        unset($DB);        
        return $result;
    }
    
    public static function CreateSoftwareDescription(SoftwareDescription $sd)
    {
        $DB = self::create();
        $count = $this->DB->InsertArray($this->DB->DB(), "software", $sd->StorableData());
        unset($DB);
        return ($count > 0);
    }
    
//    public static function CreateSoftwareVersion(SoftwareVersion $sv)
//    {
//        $DB = self::create();
//        $count = $this->DB->InsertArray($this->DB->DB(), "softwareversion", $sv->StorableData());
//        unset($DB);
//        return ($count > 0);
//    }
    
    public static function CreateSoftwareVersion($softwareID, $versionNumber , $date, $fileLocation)
    {
        $DB = self::create();
        $sql = "INSERT INTO softwareversion (SoftwareID,VersionNumber,UpdateDate,FileLocation) VALUES ('{$softwareID}','{$versionNumber}','{$date}','{$fileLocation}')";
        $sql_result = $DB->DB->insert($sql);
        unset($DB);
        return $sql_result;
    }    
    
    public static function GetUserLevelList()
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM userlevel");
        foreach ($sql_result as $key => $userLevel_row)
            $result[$key] = UserLevel::createFromArray($userLevel_row);    

        unset($DB);        
        return $result;
    }
    
    public static function GetCategoryList()
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM category");
        foreach ($sql_result as $key => $category_row)
            $result[$key] = Category::createFromArray($category_row);    

        unset($DB);        
        return $result;
    }
    
    public static function SoftwareByID($id)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM software WHERE SoftwareID = {$id} limit 1");
        $U = SoftwareDescription::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function SoftwareVersionLatestBySoftwareID($id)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = {$id} ORDER BY SoftwareVersionID DESC LIMIT 1");
        $U = SoftwareVersion::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function SoftwareVersionBySoftwareID($id)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareID = {$id} limit 1");
        $U = SoftwareVersion::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function SoftwareVersionByVersionID($id)
    {
        $DB = self::create();
        $sql_result = $DB->query("SELECT * FROM softwareversion WHERE SoftwareVersionID = {$id} limit 1");
        $U = SoftwareVersion::createFromArray($sql_result[0]);  
        unset($DB);
        return $U;
    }
    
    public static function DeleteSoftwareVersion($id)
    {
        $DB = self::create();
        $sql = "DELETE FROM softwareversion WHERE SoftwareVersionID={$id}";
        $sql_result = $DB->query($sql);
        unset($DB);
        return $sql_result;
    }
    
    public static function DeleteSoftware($id)
    {
        $DB = self::create();
        $sql = "DELETE FROM software WHERE SoftwareID={$id}";
        $sql_result = $DB->query($sql);
        unset($DB);
        return $sql_result;
    }
	
    public static function DeleteUser($username)
    {
        $DB = self::create();
        $sql = "DELETE FROM user WHERE Username='{$username}'";
        $sql_result = $DB->query($sql);
        unset($DB);
        return $sql_result;
    }
}

?>
